<?php 
if(! function_exists('array_column'))
{
  function array_column($input, $columnKey, $indexKey = NULL)
  {
    $columnKeyIsNumber = (is_numeric($columnKey)) ? TRUE : FALSE;
    $indexKeyIsNull = (is_null($indexKey)) ? TRUE : FALSE;
    $indexKeyIsNumber = (is_numeric($indexKey)) ? TRUE : FALSE;
    $result = array();

    foreach ((array)$input AS $key => $row)
    { 
      if ($columnKeyIsNumber)
      {
        $tmp = array_slice($row, $columnKey, 1);
        $tmp = (is_array($tmp) && !empty($tmp)) ? current($tmp) : NULL;
      }
      else
      {
        $tmp = isset($row[$columnKey]) ? $row[$columnKey] : NULL;
      }
      if ( ! $indexKeyIsNull)
      {
        if ($indexKeyIsNumber)
        {
          $key = array_slice($row, $indexKey, 1);
          $key = (is_array($key) && ! empty($key)) ? current($key) : NULL;
          $key = is_null($key) ? 0 : $key;
        }
        else
        {
          $key = isset($row[$indexKey]) ? $row[$indexKey] : 0;
        }
      }
      $result[$key] = $tmp;
    }
    return $result;
  }
}

function create_xls($data,$filename='simple.xls'){
    ini_set('max_execution_time', '0');
    Vendor('PHPExcel.PHPExcel');
    $filename=str_replace('.xls', '', $filename).'.xls';
    $objPHPExcel = new PHPExcel();

                     $objPHPExcel->getProperties()  //获得文件属性对象，给下文提供设置资源
                                ->setCreator( "MaartenBalliauw")             //设置文件的创建者
                                ->setLastModifiedBy( "MaartenBalliauw")       //设置最后修改者
                                ->setTitle( "Office2007 XLSX Test Document" )    //设置标题
                                ->setSubject( "Office2007 XLSX Test Document" )  //设置主题
                                ->setDescription( "Test document for Office2007 XLSX, generated using PHP classes.") //设置备注
                                ->setKeywords( "office 2007 openxmlphp")        //设置标记
                                ->setCategory( "Test resultfile");                //设置类别

    $objPHPExcel->getActiveSheet()->setTitle('Simple');
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); 
    $objPHPExcel->getActiveSheet()->fromArray($data);
    $objPHPExcel->setActiveSheetIndex(0);
    $ua = $_SERVER["HTTP_USER_AGENT"];
    
    $datetime = date('Y-m-d', time());        
    if (preg_match("/MSIE/", $ua)) {
        $filename = urlencode($filename); //处理IE导出名称乱码
    }             
    // excel头参数  
    header('Content-Type: application/vnd.ms-excel');  
    header('Content-Disposition: attachment;filename="'.$filename.'.xls"');  //日期为文件名后缀  
    header('Cache-Control: max-age=0'); 
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//excel5为xls格式，excel2007为xlsx格式  
    
    $objWriter->save('php://output');

}

/** 
 * 数据导出 
 * @param array $tit     标头 
 * @param array $title   标题行名称 
 * @param array $data   导出数据 
 * @param string $fileName 文件名 
 * @return string   
 * @throws PHPExcel_Exception 
 * @throws PHPExcel_Reader_Exception 
 */ 

function exportExcel($tit,$title=array(), $data=array(), $fileName=''){  
    Vendor('PHPExcel.PHPExcel'); 
    $obj = new PHPExcel();  
    //横向单元格标识  
    $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');  
    $obj->getActiveSheet(0)->setTitle('耗材领用表');   //设置sheet名称 
    $obj->getActiveSheet(0)->freezePaneByColumnAndRow(2,3); //冻结单元格 列数，行数

    $_row = 1;   //设置纵向单元格标识  

    if($title){  
        $_cnt = count($title);  
        $obj->getActiveSheet(0)->mergeCells('A'.$_row.':'.$cellName[$_cnt-1].$_row);   //合并单元格
        $obj->getActiveSheet(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
        $obj->setActiveSheetIndex(0)->setCellValue('A'.$_row, $tit.date('Y-m-d H:i'));  //设置合并后的单元格内容  
        $_row++;
        $i = 0;  
        foreach($title AS $v){   //设置列标题  
            $obj->setActiveSheetIndex(0)->setCellValue($cellName[$i].$_row, $v);  
            $i++;  
        }  
        $_row++;  
    }
    //填写数据  
    if($data){
        $i = 0;
        foreach($data AS $_v){
            $j = 0;
            foreach($_v AS $_cell){
                    $obj->getActiveSheet(0)->setCellValue($cellName[$j] . ($i+$_row), $_cell);  
                $j++;
            }  
            $i++;
        }  
    }  
    //文件名处理  
    if(!$fileName){
        $fileName = uniqid(time(),true);
    }

    $objWrite = PHPExcel_IOFactory::createWriter($obj, 'Excel5');

    $datetime = date('Y-m-d', time());

    if (preg_match("/MSIE/", $ua)) {
        $filename = urlencode($filename); //处理IE导出名称乱码
    }
    //网页下载  
    header('pragma:public');  
    header('Content-Disposition:attachment;filename="'.$fileName.$datetime.'.xls"');  //日期为文件名后缀  
    $objWrite->save('php://output');exit;
    $_fileName = iconv("utf-8", "gb2312", $fileName);   //转码  
 
}


/** 
*  数据导入 
* @param string $file excel文件 
* @param string $sheet 
 * @return string   返回解析数据 
 * @throws PHPExcel_Exception 
 * @throws PHPExcel_Reader_Exception 
*/ 

function importExecl($file='', $sheet=0){  
    Vendor('PHPExcel.PHPExcel');  //引入PHP EXCEL类  
    $file = iconv("utf-8", "gb2312", $file);   //转码  
    if(empty($file) OR !file_exists($file)) {  
        die('file not exists!');  
    } 

    $objRead = new PHPExcel_Reader_Excel2007();   //建立reader对象  
    if(!$objRead->canRead($file)){  
        $objRead = new PHPExcel_Reader_Excel5();  
        if(!$objRead->canRead($file)){  
            die('No Excel!');  
        }  
    }  
    $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');  
    $obj = $objRead->load($file);  //建立excel对象  
    $currSheet = $obj->getSheet($sheet);   //获取指定的sheet表  
    $columnH = $currSheet->getHighestColumn();   //取得最大的列号  
    $columnCnt = array_search($columnH, $cellName);  
    $rowCnt = $currSheet->getHighestRow();   //获取总行数  
    $data = array();  
    for($_row=1; $_row<=$rowCnt; $_row++){  //读取内容  
        for($_column=0; $_column<=$columnCnt; $_column++){  
            $cellId = $cellName[$_column].$_row;  
            $cellValue = $currSheet->getCell($cellId)->getValue();  
             //$cellValue = $currSheet->getCell($cellId)->getCalculatedValue();  #获取公式计算的值  
            if($cellValue instanceof PHPExcel_RichText){   //富文本转换字符串  
                $cellValue = $cellValue->__toString();  
            }    
            $data[$_row][$cellName[$_column]] = $cellValue;  
        }  
    }  
    return $data;  
}